package com.xgry.bpm.api.base.sqlservice.service.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.xgry.bpm.api.base.createtable.entity.CreateTable;
import com.xgry.bpm.api.base.createtable.mapper.CreateTableMapper;
import com.xgry.bpm.api.base.dbinfo.entity.Dbinfo;
import com.xgry.bpm.api.base.dbinfo.mapper.DbinfoMapper;
import com.xgry.bpm.api.base.formltem.entity.FormLtem;
import com.xgry.bpm.api.base.formltem.mapper.FormLtemMapper;
import com.xgry.bpm.api.base.sqldata.service.SqlData;
import com.xgry.bpm.api.base.sqlservice.service.SqlService;
import com.xgry.bpm.api.base.tableclass.entity.TableClass;
import com.xgry.bpm.api.base.tableclass.mapper.TableClassMapper;
import com.xgry.bpm.api.business.coding.dao.CodingDao;
import com.xgry.bpm.api.business.coding.entity.Coding;
import com.xgry.bpm.api.business.defaultfield.dao.DefaultFieldDao;
import com.xgry.bpm.api.business.defaultfield.entity.DefaultField;
import com.xgry.bpm.api.business.model.dao.ModelDao;
import com.xgry.bpm.api.business.model.entity.Model;
import com.xgry.bpm.common.exceptions.JdbcException;
import com.xgry.bpm.common.utils.DateTimeUtil;
import com.xgry.bpm.config.datasouce.oldsource.DataDBServer;
import com.xgry.bpm.config.datasouce.oldsource.MSFactory;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpSession;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
@Slf4j
@Transactional
public class SqlServiceImpl implements SqlService {

    @Autowired
    private FormLtemMapper formItemsDao;
    @Autowired
    private SqlData sqlData;
    @Autowired
    private TableClassMapper tableClassDao;
    @Autowired
    private CreateTableMapper createTableDao;
    @Autowired
    private ModelDao modelDao;
    @Autowired
    private DbinfoMapper dbInfoDao;
    @Autowired
    private DefaultFieldDao defaultFieldDao;
    @Autowired
     private MSFactory msFactory;
    @Autowired
     private DataDBServer dbServer;
    @Autowired
     private CodingDao codingDao;
    int cc=0;

    /**
     * 主表具体数据内容查询
     * @param formListId
     * @return
     * @throws Exception
     */
    @Override
    public JSONArray findsAll(int formListId) throws Exception {
        JSONArray jsonArray = sqlData.findsAll(formListId);
        return jsonArray;
    }

    @Override
    public JSONArray findsPage(int formListId, Integer pageIndex, Integer pageSize) throws Exception {
        JSONArray jsonArray = sqlData.findsPage(formListId,pageIndex,pageSize);
        return jsonArray;
    }

    @Override
    public void createTable(JSONObject jsonObject) throws Exception {
        sqlData.newCreateTable(jsonObject);
    }

    @Override
    public void createTableKey(JSONObject jsonObject) throws Exception {
        sqlData.createKey(jsonObject);
    }

    @Override
    public JSONObject deleteTable(JSONArray jsonArray) throws Exception {
        JSONObject reJSON = new JSONObject();
        try {
            JSONArray dataJSONArray = new JSONArray();
            List<FormLtem> formltemsList = formItemsDao.selectList(new QueryWrapper<>());
            boolean b =true;
            for (int i = 0; i < jsonArray.size(); i ++ ){
                JSONObject jsonObject = jsonArray.getJSONObject(i);
                String cTableName = jsonObject.getString("cTableName");
                for (int j = 0; j < formltemsList.size(); j ++){
                    FormLtem formltems = formltemsList.get(j);
                    if (!"{}".equals(formltems.getRef())){
                        JSONObject refJSON = JSON.parseObject(formltems.getRef());
                        JSONObject ref = JSON.parseObject(refJSON.getString("ref"));
                        String refTableName = ref.getString("cTableName");
                        if (cTableName.equals(refTableName)){
                            JSONObject cJSON = new JSONObject();
                            System.out.println(formltems.getCTableName());
                            System.out.println(formltems.getCTableField());
                            CreateTable createTable = createTableDao.selectOne(new QueryWrapper<CreateTable>(){{
                                this.eq("tableName",formltems.getCTableName());
                                this.eq("field",formltems.getCTableField());
                            }});
                            cJSON.put("cTableName",formltems.getCTableName());
                            cJSON.put("field",formltems.getCTableField());
                            cJSON.put("value",jsonObject.getInteger("id"));
                            cJSON.put("type",createTable.getType());
                            System.out.println("cJSON:"+cJSON);
                            dataJSONArray = sqlData.referenceTest(cJSON);
                            System.out.println("dataJSONArray.size():" + dataJSONArray.size());
                            System.out.println("ss"+b);
                            if (dataJSONArray.size() >0){
                                System.out.println("suv+"+formltems.getCTableName());
                                System.out.println(""+jsonObject.getInteger("id")+"");
                                b = false;
                                break;
                            }
                        }
                    }
                }
            }
            System.out.println("-------------------------------------:"+b);
            if (b==true){
                sqlData.delete(jsonArray);
            }else{
                reJSON.put("state","error");
                reJSON.put("message","当前数据已被引用，不可删除");
            }
        }catch (Exception e){
            System.out.println("dddd"+e.getMessage());
            log.error(e.getMessage());
        }
        return reJSON;
    }

    @Override
    public void createSTable(JSONObject jsonObject) throws Exception {
        try{
            TableClass tableClass = new TableClass();
            tableClass.setTableClassName(jsonObject.getString("cTableName"));
            tableClass.setTableChClassName(jsonObject.getString("cTableChName"));
            tableClass.setMTableId(jsonObject.getInteger("pid"));
            Model model = modelDao.selectById(jsonObject.getInteger("model"));
            tableClass.setModelId(model.getId());
            Dbinfo dbInfo = dbInfoDao.selectById(jsonObject.getInteger("dbInfoId"));
            tableClass.setDbInfoId(dbInfo.getId());
            tableClass.setPid(jsonObject.getInteger("pid"));

            tableClassDao.insert(tableClass);
            //创建子表json
            TableClass mTableClass = tableClassDao.selectById(jsonObject.getInteger("pid"));
            JSONObject createStable = new JSONObject();
            JSONObject createStableJSON = new JSONObject();
            createStable.put("cTableName",jsonObject.getString("cTableName"));
            createStable.put("dbinfo_id",jsonObject.getInteger("dbInfoId"));
            createStable.put("mTableName",mTableClass.getTableClassName());
            createStableJSON.put("data",createStable);
            sqlData.createStable(createStableJSON);

            List<DefaultField> definitions = defaultFieldDao.selectList(new QueryWrapper<>());
            List<CreateTable> createTables = new ArrayList<>();
            if (definitions.size() > 0){
                for (int i = 0; i < definitions.size(); i ++){
                    DefaultField defaultField = definitions.get(i);
                    CreateTable createTable = new CreateTable();
                    if ("parent_level".equals(defaultField.getFieldName())){
                        createTable.setField(defaultField.getFieldName());
                    }else{
                        createTable.setField(tableClass.getTableClassName() + "_" + defaultField.getFieldName());
                    }
                    createTable.setState("1");
                    createTable.setFieldCh(defaultField.getFieldChName());
                    createTable.setType(defaultField.getFieldType());
                    createTable.setTableName(jsonObject.getString("cTableName"));
                    if (defaultField.getFieldType() == "int"){
                        createTable.setFileTypeCh("整数");
                    }
                    if(defaultField.getFieldType() == "varchar(8000)"){
                        createTable.setFileTypeCh("短文本");
                    }
                    if(defaultField.getFieldType() == "text"){
                        createTable.setFileTypeCh("长文本");
                    }
                    if(defaultField.getFieldType() == "datetime"){
                        createTable.setFileTypeCh("日期");
                    }
                    if(defaultField.getFieldType() == "numeric(18,5)"){
                        createTable.setFileTypeCh("数值");
                    }
                    createTable.setTableClassId(tableClass.getTableClassId());
                    createTables.add(createTable);
                }
//                CreateTable createTable = new CreateTable();
//                createTable.setField(jsonObject.getString("cTableName") + "_id");
//                createTable.setFieldCh("主键id");
//                createTable.setTableClass(tableClass);
//                createTable.setType("int");
//                createTable.setTableName(tableClass.getTableClassName());
//                createTables.add(createTable);
                createTables.forEach(s->createTableDao.insert(s));

            }
        }catch (Exception e){
            log.error(e.getMessage());
        }
    }

    @Override
    public JSONObject insertData(JSONArray jsonArray) throws JdbcException {
        JSONObject jsonObject = new JSONObject();
        try {
            jsonObject = dataTypeChecking(jsonArray);

            JSONObject jsonObject1 = jsonArray.getJSONObject(0);
            System.out.println("jsobjet1"+jsonObject1);
            String tableName = jsonObject1.getString("cTableName");
            JSONObject jsonObject2=jsonObject1.getJSONObject("obj");


            if (!"error".equals(jsonObject.getString("state"))) {

                jsonObject = sqlData.insertData(jsonArray);

            }



            String uid = jsonObject2.getString("PersonnelFiles_OperatorCode");
            String name = jsonObject2.getString("PersonnelFiles_OperatorName");

            //反向添加user_info表
            if (tableName.equals("PersonnelFiles") && !uid.equals("") && !name.equals("")) {
                insertusr(jsonArray);
            }
            if(tableName.equals("srpayment")){
                insertImprest(jsonArray);
            }
        }catch (Exception e){
            throw new JdbcException(e.getMessage());
        }

        return jsonObject;
    }

    @Override
    public JSONObject updateData(JSONArray jsonArray)throws Exception{
        System.out.println("updateData:"+jsonArray);
        JSONObject jsonObject = new JSONObject();
        try {
            jsonObject = dataTypeChecking(jsonArray);
            JSONObject jsonObject1=jsonArray.getJSONObject(0);
            System.out.println("jsobject"+jsonObject);
            String tablename=jsonObject1.getString("cTableName");
            JSONObject jsonObject2=jsonObject1.getJSONObject("obj");
            //反向修改user_info表
            if(tablename.equals("PersonnelFiles")){
                insertusr(jsonArray);
            }
            if (!"error".equals(jsonObject.getString("state"))){

                jsonObject =  sqlData.updateData(jsonArray);
            }


        }catch (Exception e){
            throw new JdbcException(e.getMessage());
        }
        return jsonObject;
    }

    @Override
    public void newCreateField(JSONObject jsonObject) throws Exception {
        CreateTable createTable = new CreateTable();
        createTable.setTableName(jsonObject.getString("tableName"));
        TableClass tableClass = tableClassDao.selectOne(new QueryWrapper<TableClass>() {{
            this.eq("tableClassName", createTable.getTableName());
        }});
        createTable.setTableClassId(tableClass.getTableClassId());
        String newField = jsonObject.getString("tableName") + "_" +jsonObject.getString("field");
        createTable.setField(jsonObject.getString("tableName") + "_" +jsonObject.getString("field"));
        createTable.setFieldCh(jsonObject.getString("fieldCh"));
        createTable.setType(jsonObject.getString("type"));
        createTable.setState(jsonObject.getString("state"));
        if (jsonObject.getString("notNull") == "false"){
            createTable.setNotNull(jsonObject.getString("notNull"));
        }
        List<CreateTable> createTables = new ArrayList<>();
        createTables.add(createTable);
        createTables.forEach(s->createTableDao.insert(s));
        jsonObject.put("field",newField);
        sqlData.newCreateField(jsonObject);
    }

    @Override
    public void createKey(JSONObject jsonObject) throws Exception {

    }

    @Override
    public JSONArray findAll(int formListId) throws Exception {

        JSONArray jsonArray = sqlData.findAll(formListId);
        return jsonArray;

    }

    @Override
    public JSONArray findById(JSONObject jsonObject) throws Exception {
        JSONArray jsonArray = sqlData.findById(jsonObject);
        return jsonArray;
    }

    @Override
    public String Select() throws Exception {
        return  sqlData.selected();
    }


    @Override
    public JSONArray casaedNew(JSONObject jsonObject) throws Exception {
        return sqlData.casaedNew(jsonObject);
    }

    @Override
    public JSONObject Selected(JSONArray jsonArray) throws Exception {
        JSONObject jsonObject = new JSONObject();
        JSONObject jsonObject0=jsonArray.getJSONObject(0);
        String tableName = jsonObject0.getString("cTableName");
        JdbcTemplate msJdbc = msFactory.getJdbcTemplate();
        List<Map<String, Object>> maps = msJdbc.queryForList("select * from table_class where tableClassName = '" + tableName + "'");
        JSONArray cols = JSON.parseArray(JSON.toJSONString(maps));
        JSONObject jsjson = cols.getJSONObject(0);
        JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(jsjson.getInteger("dbInfoId"));
        JSONObject jo = jsonArray.getJSONObject(0);
        JSONObject jo1 = jo.getJSONObject("obj");
        String ApplicationDepartment = jo1.getString("ExpensesReimbursement_ApplicationDepartment");
        for (int i = 1; i < jsonArray.size(); i++) {
            JSONObject json = jsonArray.getJSONObject(i);
            JSONObject jy = json.getJSONObject("obj");
            String tablename1 = json.getString("cTableName");
            String money0 = jy.getString("ReimbursementInformation_xgry_0003");
            System.out.println("jy：" + jy);
            String project = jy.getString("ReimbursementInformation_xgry_0001");
             List<Map<String, Object>> mlist=jdbcTemplate.queryForList("SELECT * from "+tablename1+" left  join "+tableName+" on "+tablename1+".ExpensesReimbursement_id="+tableName+".ExpensesReimbursement_id where " + tableName + ".ExpensesReimbursement_ApplicationDepartment='"+ApplicationDepartment+"' and   "+tablename1+".ReimbursementInformation_xgry_0001='"+project+"'");
			  if(mlist.size()>0){

                      List<Map<String, Object>> o = jdbcTemplate.queryForList("select sum (" + tablename1 + ".ReimbursementInformation_xgry_0003) xgry_003," + tableName + ".ExpensesReimbursement_ApplicationDepartment ea," + tablename1 + ".ReimbursementInformation_xgry_0001 xgry_0001 from  " + tablename1 + " left join " + tableName + " on " + tablename1 + ".ExpensesReimbursement_id=" + tableName + ".ExpensesReimbursement_id   where  " + tableName + ".ExpensesReimbursement_ApplicationDepartment='" + ApplicationDepartment + "' and   " + tablename1 + ".ReimbursementInformation_xgry_0001='" + project + "' GROUP BY " + tableName + ".ExpensesReimbursement_ApplicationDepartment," + tablename1 + ".ReimbursementInformation_xgry_0001");

                      JSONArray jsonarray = JSON.parseArray(JSON.toJSONString(o));
                      JSONObject jsonObject2 = jsonarray.getJSONObject(0);
                      String money = jsonObject2.getString("xgry_003");
                      String ApplicationDepartment1 = jsonObject2.getString("ea");
                      String project1 = jsonObject2.getString("xgry_0001");
                      List<Map<String, Object>> mapList = jdbcTemplate.queryForList("select * from  BudgetApplicationChile left join  BudgetApplication on BudgetApplicationChile.BudgetApplication_id=BudgetApplication.BudgetApplication_id where BudgetApplication.BudgetApplication_ApplicationDepartment='" + ApplicationDepartment1 + "' and BudgetApplicationChile.BudgetApplicationChile_BudgetItems='" + project1 +"'");
                      JSONArray jsonArray3 = JSON.parseArray(JSON.toJSONString(mapList));
                      JSONObject jsonObject3 = jsonArray3.getJSONObject(0);
                      String money1 = jsonObject3.getString("BudgetApplicationChile_BudgetAmount");
                      String ApplicationDepartment2 = jsonObject3.getString("BudgetApplication_ApplicationDepartment");
                      String project2 = jsonObject3.getString("BudgetApplicationChile_BudgetItems");
                      double summoney=Double.parseDouble(money)+Double.parseDouble(money0);
                  jsonObject.put("sum",summoney);
                  jsonObject.put("budgetmoney",money1);
                  jsonObject.put("nowmoney",money0);
                  jsonObject.put("alreadymoney",money);
                  jsonObject.put("ApplicationDepartment",ApplicationDepartment);
                  jsonObject.put("project",project);
                      if (summoney >Double.parseDouble(money1)){
                          jsonObject.put("fail", "部门是"+ApplicationDepartment+"项目内容为"+project+"申请额度超出预期");
                          break;
                      }
              }
        }

        System.out.println("js"+jsonObject);
           return jsonObject;
    }

    @Override
    public void insertusr(JSONArray jsonArray) throws Exception {
        sqlData.insertuser(jsonArray);
    }

    @Override
    public void deletefield( String tablename,String field) throws Exception {
        JSONObject jsonObject=new JSONObject();
        sqlData.deleteField(tablename,field);
    }


    private JSONObject dataTypeChecking(JSONArray jsonArray)throws Exception{
        JSONObject returnObject = new JSONObject();
        for(int i = 0; i < jsonArray.size(); i ++){
            JSONObject jsonObject = jsonArray.getJSONObject(i);
            String cTableName = jsonObject.getString("cTableName");
            String obj = jsonObject.getString("obj");
            JSONObject objJson = JSON.parseObject(obj);
            objJson.remove("id");
            objJson.remove("_id");
            objJson.remove("_uid");
            objJson.remove("mTableId");
            for (Map.Entry<String, Object> entry : objJson.entrySet()) {
                CreateTable createTable = createTableDao.selectOne(new QueryWrapper<CreateTable>(){{
                    this.eq("tableName",cTableName);
                    this.eq("field",entry.getKey());
                }});
                if (createTable != null){
                    try{
                        if ("varchar(8000)".equals(createTable.getType())&& !"".equals(entry.getValue())){
                            entry.getValue().toString();
                        }
                        if ("int".equals(createTable.getType())&& !"".equals(entry.getValue())){
                            Integer b = Integer.parseInt(entry.getValue().toString());
                        }
                        if ("datetime".equals(createTable.getType())&& !"".equals(entry.getValue())){
                            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                            Date c = format.parse(entry.getValue().toString());
                        }
                        if ("numeric(18,5)".equals(createTable.getType())&& !"".equals(entry.getValue())){
//                              Double d = (Double) entry.getValue();
//
//
//
//
//                          java.math.BigDecimal c=(java.math.BigDecimal)entry.getValue();
//
//
//                               Double d = (double)entry.getValue();



//                           BigDecimal c = (BigDecimal)entry.getValue();
                            String s = entry.getValue().toString();
                            Double.parseDouble(s);

                        }
                        if ("text".equals(createTable.getType())&& !"".equals(entry.getValue())){
                            entry.getValue().toString();
                        }
                        if (createTable.getNotNull() != null && !"".equals(entry.getValue())){
                            returnObject.put("state","error");
                            returnObject.put("message",entry.getKey() + "/"+ createTable.getFieldCh()+"为非空字段");
                            break;
                        }
                    }catch (Exception e){
                        returnObject.put("state","error");
                        returnObject.put("message",entry.getKey() + "/"+ createTable.getFieldCh()+"数据类型转换失败，请检查填写内容");
                        break;
                    }
                }
            }
        }
        return returnObject;
    }

    /**
     * 流水号生成规则
     * @return
     */
    public String findCode(String str)throws Exception{
        String s = sqlData.findCode(str);
        return s;
    }

    @Override
    public String findSum(String str) throws Exception {
        String s = sqlData.findSum(str);
        return s;
    }

    @Override
    public String findFormState(String str) throws Exception {
        String s = sqlData.findFormState(str);
        return s;
    }

    @Override
    public JSONArray findByParentLevel(String tableName, String parentLevel) throws Exception {
        return sqlData.findByParentLevel(tableName, parentLevel);
    }

    @Override
    public JSONArray findByLike(int formListId, String findQuery) throws Exception {
        List<Map<String,Object>> mapList = sqlData.findByLike(formListId, findQuery);
        JSONArray jsonArray = JSON.parseArray(JSON.toJSONString(mapList));
        return jsonArray;
    }

    @Override
    public JSONArray casefind(int formListId) throws Exception {
        JSONArray jsonArray =  sqlData.casefind(formListId);

        return jsonArray;
    }

    @Override
    public void insertImprest(JSONArray jsonArray) throws Exception {
        sqlData.insertImprest(jsonArray);
    }

    @Override
    public String insertExpensesReimbursementData(JSONArray jsonArray) throws Exception {
        return null;
    }

    @Override
    public JSONArray findFliter(String str) throws Exception {
        List<Map<String, Object>> fliter = sqlData.findFliter(str);
        JSONArray jsonArray = JSONArray.parseArray(JSON.toJSONString(fliter));
        return jsonArray;
    }

    // 复制功能更改单据编号id
    @Override
    public JSONArray updateCodeId(JSONArray jsonArray, JSONObject jsonObject) throws Exception {

        //JSONObject jsonObject = JSONObject.parseObject(String.valueOf(jsonArray));
        String cTableName = jsonObject.getString("cTableName");
        String formId = jsonObject.getString("formId");
        int fromid = Integer.valueOf(formId);
        Coding codingByFormId = codingDao.selectById(fromid);
        Coding coding = new Coding();
        if (codingByFormId == null) {
            coding.setCoding("0000000001");
            coding.setFormId(fromid);
            int id = codingDao.insert(coding);

            JSONObject jsonObject1 = JSONObject.parseObject(String.valueOf(jsonArray));
            jsonObject1.put(cTableName + "_code",id);
            JSONArray jsonArray1 = JSONArray.parseArray(String.valueOf(jsonObject1));
            //json.put("codingId", id);
        } else {
            String code = codingByFormId.getCoding();
            String coding2 = null;
            Integer i = Integer.parseInt(code);
            i++;
            int length = i.toString().length();

            if (length == 10) {
                coding2 = i.toString();
            } else {
                StringBuffer sb = new StringBuffer();
                for (int j = 0; j < 10 - length; j++) {
                    sb.append("0");
                }
                coding2 = sb.toString() + i.toString();
            }
            coding.setCoding(coding2);
            coding.setFormId(fromid);
            int id = codingDao.insert(coding);

            JSONObject jsonObject2 = jsonArray.getJSONObject(0);
            JSONObject jsonObject1 = jsonObject2.getJSONObject(cTableName);
            jsonObject1.put(cTableName+"_code",coding2);
            jsonObject2.put(cTableName,jsonObject1);
            //json.put("codingId", id);
        }
        return jsonArray;
    }

    /**
     * 通过日期计算天数
     * @param str
     * @return
     */
    @Override
    public String day(String str) throws Exception {

        JSONObject jsonObject = JSONObject.parseObject(str);
        String dayStr = (String) jsonObject.get("dayStr");
        String fieldN = (String) jsonObject.get("fieldN");

        long day;
        String substring = dayStr.substring(1, dayStr.length() - 1);

        String endtime = substring.substring(0,10);  //结束日期
        String bengintime = substring.substring(11);//开始日期


        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyy-MM-dd");
        Date endTime = simpleDateFormat.parse(endtime); //结束日期
        Date beginTime = simpleDateFormat.parse(bengintime); //开始日期

        day = (endTime.getTime() - beginTime.getTime()) / (24*60*60*1000);
        if (fieldN.equals("TravelSubsidy_xgry_0005") && fieldN != "") { //补贴标准
            day = day + 1;
        }
        return String.valueOf(day);
    }

    /**
     * beginTime //开始日期
     * endTime //结束日期
     * province //省市
     * area //地区
     * selectByTableName //标准金额的表名
     * @param str
     * @return
     */
    @Override
    public String findStandardAmount(String str) throws Exception {
        JSONArray jsonArray = new JSONArray();
        JSONObject jsonObject = JSONObject.parseObject(str);

        String standardAmountField = (String) jsonObject.get("a"); // 需要赋值的报销标准金额字段

        String beginTime = (String) jsonObject.get("beginTime");    // 开始日期
        String endTime = (String) jsonObject.get("endTime");        // 结束日期
        String province = (String) jsonObject.get("province");      // 省市
        String area = (String) jsonObject.get("area");              // 地区
        String selectByTableName = (String) jsonObject.get("selectByTableName");// 标准金额表名

        //  日期格式转换
//        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyy-MM-dd");
//        if (beginTime != null && beginTime != "") {
//            Date begin = simpleDateFormat.parse(beginTime);
//        }
//        if (endTime != null && endTime != "") {
//            Date end = simpleDateFormat.parse(endTime);
//        }

//        System.out.println(begin);
//        System.out.println(end);
        System.out.println(province);
        System.out.println(area);
        System.out.println(selectByTableName);

        /**
         * 根据standardAmountField(标准金额字段)去对应的标准表查询
         * 1.住宿标准：通过省市地区和日期查询标准(日期分淡季旺季标准不同)
         * 2.差旅补贴标准：通过省市地区不同进行查询标准
         */
        String amount = "";
        if (standardAmountField.indexOf("DetailsOfReimbursementForAccommodationExpenses") > -1) { // 住宿标准金额查询
            // 查询差旅住宿标准金额
            amount = sqlData.selectRstandardAmount(beginTime,endTime,province,area,selectByTableName);
        } else if (standardAmountField.indexOf("TravelSubsidy") > -1) { // 差旅补贴金额查询
            // 查询差旅补贴标准金额
            amount = sqlData.selectTravelSubsidyStandardAmount(province,area,selectByTableName);
        }

        return amount;
    }

    /**
     * 查询所有终审单据
     * @return
     */
    @Override
    public JSONArray findAllFinalDocument() throws Exception {
        // 终审单据为日常业务的申请单据最后终审的状态 （需要进行付款的单据）

        // 查询报销管理下的所有主表单据对应的表名(包括中文名)
        List<Map<String,Object>> tableNameList = sqlData.findTableName();

        // 查询终审单据
        JSONArray jsonArray = sqlData.selectFinalDocument(tableNameList);

//        // 通过model表的模块名称modelName关联查询form_list中的 付款表对应的formlistId
//        List<Map<String,Object>> list = sqlData.selctDocumentPayment(tableNameList);
//
//        // 通过list中的终审单据表查询对应单据中已终审的数据
//        JSONArray jsonArray = sqlData.findFinalDocument(list);



        return jsonArray;
    }

    @Override
    public JSONArray findAllTask(HttpSession session) throws Exception {
        // 终审单据为日常业务的申请单据最后终审的状态 （需要进行付款的单据）
        // 查询报销管理下的所有主表单据对应的表名(包括中文名)
        List<Map<String,Object>> tableNameList = sqlData.findTableName();
        JSONArray jsonArray = sqlData.selectTask(tableNameList,session);
                return jsonArray;
    }

    @Override
    public JSONArray findAllTaskByCondition(HttpSession session, JSONObject jsonStr) throws Exception {
        // 终审单据为日常业务的申请单据最后终审的状态 （需要进行付款的单据）
        // 查询报销管理下的所有主表单据对应的表名(包括中文名)
        List<Map<String,Object>> tableNameList = sqlData.findTableName();
        JSONArray jsonArray = sqlData.selectTaskTwo(tableNameList,session,jsonStr);
        return jsonArray;
    }

    @Override
    public int count(int formListId) throws Exception {
        return sqlData.count(formListId);
    }


    /**
     *
     * @param jsonObject
     * @return
     */
    @Override
    public JSONArray selectDocumentsContent(JSONObject jsonObject) throws Exception {

        JSONArray jsonArray = new JSONArray();
        String tableName = (String) jsonObject.get("tableName");// 选中单据的表名
        //String tableChClassName = (String) jsonObject.get("tableChClassName");// 选中单据的中文表名
        JSONObject selectedData = (JSONObject) jsonObject.get("selectedData");
        int formId = (int) jsonObject.get("fromListId");


        JdbcTemplate jdbcTemplate = dbServer.getJdbcTemplate(2);
        //JSONObject json = new JSONObject();
        Coding codingByFormId =  codingDao.selectOne(new QueryWrapper<Coding>(){{
            this.eq("formId",formId);
        }});
        Coding coding = new Coding();
        if (codingByFormId == null) {
            coding.setCoding("0000000001");
            coding.setFormId(formId);
            int id = codingDao.insert(coding);
            //json.put("codingId", id);
        } else {
            String code = codingByFormId.getCoding();
            String coding2 = null;
            Integer i = Integer.parseInt(code);
            i++;
            int length = i.toString().length();

            if (length == 10) {
                coding2 = i.toString();
            } else {
                StringBuffer sb = new StringBuffer();
                for (int j = 0; j < 10 - length; j++) {
                    sb.append("0");
                }
                coding2 = sb.toString() + i.toString();
            }
            coding.setCoding(coding2);
            coding.setFormId(formId);
            int id = codingDao.insert(coding);
            //json.put("codingId", id);
        }

        // 获取不同表中的金额字段用以对应付款单的金额字段
        if ("ApplicationFund".equals(tableName)) { // 备用金申请表

            String coding1 = coding.getCoding();
            String tableChClassName = (String) jsonObject.get("tableChClassName");// 选中单据的中文表名
            String date = DateTimeUtil.dateToStr(new Date(), "yyyy-MM-dd");
            String PayMentOrder_xgry_0002 = selectedData.getString("ApplicationFund_code");
            String PayMentOrder_xgry_0003 = selectedData.getString("ApplicationFund_xgry_0003");
            String PayMentOrder_xgry_0004 = selectedData.getString("ApplicationFund_xgry_0010");

            // 根据单据类型和单据编号查询主表数据
            String selectSql = "SELECT * FROM PayMentOrder WHERE PayMentOrder_xgry_0001 = '" + tableChClassName + "' and PayMentOrder_xgry_0002 = '" + PayMentOrder_xgry_0002 + "'";
            List<Map<String, Object>> mapListSelectSql = jdbcTemplate.queryForList(selectSql);
            if (mapListSelectSql.size() > 0 ) { //有主表数据
                JSONObject jsonObject1 = new JSONObject();
                JSONArray mapListArray1 = JSON.parseArray(JSON.toJSONString(mapListSelectSql));
                JSONObject mapListJSONObject1 = mapListArray1.getJSONObject(0);

                // 获取主表数据id
                int payMentOrder_id = (int) mapListJSONObject1.get("PayMentOrder_id");

                Set<String> strings = mapListJSONObject1.keySet();
                JSONObject o = new JSONObject();
                for (String string : strings) {
                    Object o1 = mapListJSONObject1.get(string);
                    o.put(string,o1+"_"+o1);
                }
                jsonObject1.put("PayMentOrder",o);
                jsonObject1.put("cTableName","PayMentOrder");

                StringBuffer stringBuffer = new StringBuffer();
                stringBuffer.append("INSERT INTO ParticularsOfPayee (PayMentOrder_id,ParticularsOfPayee_xgry_0001,ParticularsOfPayee_xgry_0002,ParticularsOfPayee_xgry_0003,ParticularsOfPayee_xgry_0004," +
                        "ParticularsOfPayee_xgry_0005,ParticularsOfPayee_xgry_0006,ParticularsOfPayee_xgry_0007) VALUES (");
                stringBuffer.append(payMentOrder_id + "," + selectedData.getString("ApplicationFund_xgry_0007") + ",null,'" + selectedData.getString("ApplicationFund_xgry_0008") + "','" + selectedData.getString("ApplicationFund_xgry_0009") + "',null,null,null)");
                stringBuffer.append("; SET NOCOUNT ON SELECT SCOPE_IDENTITY() as ParticularsOfPayee_id");
                List<Map<String, Object>> mapListSql1 = jdbcTemplate.queryForList(stringBuffer.toString());
                JSONArray jsonArray6 = JSON.parseArray(JSON.toJSONString(mapListSql1));
                JSONObject jsonObject2 = jsonArray6.getJSONObject(0);
                Integer ParticularsOfPayee_id = jsonObject2.getInteger("ParticularsOfPayee_id");

                String sqlDate1 = "SELECT * FROM particularsOfPayee WHERE particularsOfPayee_id = " + ParticularsOfPayee_id;
                List<Map<String, Object>> mapList1 = jdbcTemplate.queryForList(sqlDate1);

                jsonObject1.put("ParticularsOfPayee",mapList1);

                jsonArray.add(jsonObject1);

            } else {
                String sql = "INSERT INTO PayMentOrder (PayMentOrder_code,PayMentOrder_billDate,PayMentOrder_xgry_0001,PayMentOrder_xgry_0002,PayMentOrder_xgry_0003,PayMentOrder_xgry_0004) values ('"
                        + coding1 + "','" + date + "','" + tableChClassName + "','" + PayMentOrder_xgry_0002 + "'," + PayMentOrder_xgry_0003 + "," + PayMentOrder_xgry_0004 + "); SET NOCOUNT ON SELECT SCOPE_IDENTITY() as PayMentOrder_id";

                List<Map<String, Object>> mapListSql = jdbcTemplate.queryForList(sql);
                JSONArray jsonArray5 = JSON.parseArray(JSON.toJSONString(mapListSql));
                JSONObject jsonObject1 = jsonArray5.getJSONObject(0);
                Integer payMentOrder_id = jsonObject1.getInteger("PayMentOrder_id");

                String sqlDate = "SELECT * FROM PayMentOrder WHERE PayMentOrder_id = " + payMentOrder_id;
                List<Map<String, Object>> mapListDate = jdbcTemplate.queryForList(sqlDate);
                JSONArray mapListArray1 = JSON.parseArray(JSON.toJSONString(mapListDate));
                JSONObject mapListJSONObject1 = mapListArray1.getJSONObject(0);
                JSONObject jsonObject11 = new JSONObject();
                Set<String> strings = mapListJSONObject1.keySet();
                JSONObject o = new JSONObject();
                for (String string : strings) {
                    Object o1 = mapListJSONObject1.get(string);
                    o.put(string,o1+"_"+o1);
                }

                JSONObject json = new JSONObject();
                json.put("PayMentOrder",o);

                json.put("cTableName","PayMentOrder");
                // 通过付款单据类型、付款单据编码查询收款人详情
                String sql2 = "SELECT * FROM ParticularsOfPayee WHERE ParticularsOfPayee_xgry_0008 = '" + tableChClassName + "' and ParticularsOfPayee_xgry_0009 = '" + selectedData.getString("ApplicationFund_code") + "'";
                List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql2);
                if (mapList.size() > 0) {
                    JSONArray mapListArray = JSON.parseArray(JSON.toJSONString(mapList));
                    JSONObject mapListJSONObject = mapListArray.getJSONObject(0);
                    String particularsOfPayee_xgry_0001 = (String) mapListJSONObject.get("ParticularsOfPayee_xgry_0001");
                    //json.put("particularsOfPayee_xgry_0001",particularsOfPayee_xgry_0001);// 收款人姓名
                    json.put("ParticularsOfPayee",mapList);
                } else {
                    StringBuffer stringBuffer = new StringBuffer();
                    stringBuffer.append("INSERT INTO ParticularsOfPayee (PayMentOrder_id,ParticularsOfPayee_xgry_0001,ParticularsOfPayee_xgry_0002,ParticularsOfPayee_xgry_0003,ParticularsOfPayee_xgry_0004," +
                            "ParticularsOfPayee_xgry_0005,ParticularsOfPayee_xgry_0006,ParticularsOfPayee_xgry_0007) VALUES (");
                    stringBuffer.append(payMentOrder_id + "," + selectedData.getString("ApplicationFund_xgry_0007") + ",null,'" + selectedData.getString("ApplicationFund_xgry_0008") + "','" + selectedData.getString("ApplicationFund_xgry_0009") + "',null,null,null)");
                    stringBuffer.append("; SET NOCOUNT ON SELECT SCOPE_IDENTITY() as ParticularsOfPayee_id");
                    //jdbcTemplate.update(stringBuffer.toString());
                    List<Map<String, Object>> mapListSql1 = jdbcTemplate.queryForList(stringBuffer.toString());
                    JSONArray jsonArray6 = JSON.parseArray(JSON.toJSONString(mapListSql1));
                    JSONObject jsonObject2 = jsonArray6.getJSONObject(0);
                    Integer ParticularsOfPayee_id = jsonObject2.getInteger("ParticularsOfPayee_id");
                    String sqlDate1 = "SELECT * FROM particularsOfPayee WHERE particularsOfPayee_id = " + ParticularsOfPayee_id;
                    List<Map<String, Object>> mapList1 = jdbcTemplate.queryForList(sqlDate1);
                    json.put("ParticularsOfPayee",mapList1);

                }
                jsonArray.add(json);
            }
        }
        if ("TravelExpense".equals(tableName)) { // 差旅费报销表
            // PayMentOrder_xgry_0001(付款单据类型)
            // PayMentOrder_xgry_0002(付款单据编码)
            // PayMentOrder_xgry_0003(应付总金额)
            // PayMentOrder_xgry_0004(实付总金额)
            // PayMentOrder_xgry_0005(剩余总金额)
            String coding1 = coding.getCoding(); // 编码
            String tableChClassName = (String) jsonObject.get("tableChClassName");// 选中单据的中文表名
            String date = DateTimeUtil.dateToStr(new Date(), "yyyy-MM-dd");
            String PayMentOrder_xgry_0002 = (String) selectedData.get("TravelExpense_code");//单据编码
            String PayMentOrder_xgry_0003 = (String) selectedData.get("TravelExpense_xgry_0012");//应付金额
            String PayMentOrder_xgry_0004 = (String) selectedData.get("");// 实付金额（差旅费表没有实付金额字段）
            String ParticularsOfPayee_xgry_0001 = (String) selectedData.get("TravelExpense_single_person");// 收款人姓名取值差旅报销表的制单人
//            String ParticularsOfPayee_xgry_0003 = (String) selectedData.get("支付银行");//
//            String ParticularsOfPayee_xgry_0004 = (String) selectedData.get("银行卡号");//

            String ParticularsOfPayee_xgry_0003 = "支付银行";//
            String ParticularsOfPayee_xgry_0004 = "银行卡号";//

            // 根据付款单据类型和付款单据编码查询主表数据
            String selectSql = "SELECT * FROM PayMentOrder WHERE PayMentOrder_xgry_0001 = '" + tableChClassName + "' and PayMentOrder_xgry_0002 = '" + PayMentOrder_xgry_0002 + "'";
            List<Map<String, Object>> mapListSelectSql = jdbcTemplate.queryForList(selectSql);
            if (mapListSelectSql.size() > 0 ) { //有主表数据
                JSONObject jsonObject1 = new JSONObject();
                JSONArray mapListArray1 = JSON.parseArray(JSON.toJSONString(mapListSelectSql));
                JSONObject mapListJSONObject1 = mapListArray1.getJSONObject(0);

                // 获取主表数据id
                int payMentOrder_id = (int) mapListJSONObject1.get("PayMentOrder_id");

                Set<String> strings = mapListJSONObject1.keySet();
                JSONObject o = new JSONObject();
                for (String string : strings) {
                    Object o1 = mapListJSONObject1.get(string);
                    o.put(string,o1+"_"+o1);
                }
                jsonObject1.put("PayMentOrder",o);
                jsonObject1.put("cTableName","PayMentOrder");

                // 新增一条付款单收款人信息
                StringBuffer stringBuffer = new StringBuffer();
                stringBuffer.append("INSERT INTO ParticularsOfPayee (PayMentOrder_id,ParticularsOfPayee_xgry_0001,ParticularsOfPayee_xgry_0002,ParticularsOfPayee_xgry_0003,ParticularsOfPayee_xgry_0004," +
                        "ParticularsOfPayee_xgry_0005,ParticularsOfPayee_xgry_0006,ParticularsOfPayee_xgry_0007) VALUES (");
                stringBuffer.append(payMentOrder_id + ",'" + ParticularsOfPayee_xgry_0001 + "',null,'" + ParticularsOfPayee_xgry_0003 + "','" + ParticularsOfPayee_xgry_0004 + "',null,null,null)");
                stringBuffer.append("; SET NOCOUNT ON SELECT SCOPE_IDENTITY() as ParticularsOfPayee_id");
                List<Map<String, Object>> mapListSql1 = jdbcTemplate.queryForList(stringBuffer.toString());
                JSONArray jsonArray6 = JSON.parseArray(JSON.toJSONString(mapListSql1));
                JSONObject jsonObject2 = jsonArray6.getJSONObject(0);
                Integer ParticularsOfPayee_id = jsonObject2.getInteger("ParticularsOfPayee_id");

                String sqlDate1 = "SELECT * FROM particularsOfPayee WHERE particularsOfPayee_id = " + ParticularsOfPayee_id;
                List<Map<String, Object>> mapList1 = jdbcTemplate.queryForList(sqlDate1);

                jsonObject1.put("ParticularsOfPayee",mapList1);

                jsonArray.add(jsonObject1);

            } else {
                String sql = "INSERT INTO PayMentOrder (PayMentOrder_code,PayMentOrder_billDate,PayMentOrder_xgry_0001,PayMentOrder_xgry_0002,PayMentOrder_xgry_0003,PayMentOrder_xgry_0004) values ('"
                        + coding1 + "','" + date + "','" + tableChClassName + "','" + PayMentOrder_xgry_0002 + "'," + PayMentOrder_xgry_0003 + "," + PayMentOrder_xgry_0004 + "); SET NOCOUNT ON SELECT SCOPE_IDENTITY() as PayMentOrder_id";

                List<Map<String, Object>> mapListSql = jdbcTemplate.queryForList(sql);
                JSONArray jsonArray5 = JSON.parseArray(JSON.toJSONString(mapListSql));
                JSONObject jsonObject1 = jsonArray5.getJSONObject(0);
                Integer payMentOrder_id = jsonObject1.getInteger("PayMentOrder_id");

                String sqlDate = "SELECT * FROM PayMentOrder WHERE PayMentOrder_id = " + payMentOrder_id;
                List<Map<String, Object>> mapListDate = jdbcTemplate.queryForList(sqlDate);
                JSONArray mapListArray1 = JSON.parseArray(JSON.toJSONString(mapListDate));
                JSONObject mapListJSONObject1 = mapListArray1.getJSONObject(0);
                JSONObject jsonObject11 = new JSONObject();
                Set<String> strings = mapListJSONObject1.keySet();
                JSONObject o = new JSONObject();
                for (String string : strings) {
                    Object o1 = mapListJSONObject1.get(string);
                    o.put(string,o1+"_"+o1);
                }

                JSONObject json = new JSONObject();
                json.put("PayMentOrder",o);

                json.put("cTableName","PayMentOrder");
                // 通过付款单据类型、付款单据编码查询收款人详情
                String sql2 = "SELECT * FROM ParticularsOfPayee WHERE ParticularsOfPayee_xgry_0008 = '" + tableChClassName + "' and ParticularsOfPayee_xgry_0009 = '" + selectedData.getString("ApplicationFund_code") + "'";
                List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql2);
                if (mapList.size() > 0) {
                    JSONArray mapListArray = JSON.parseArray(JSON.toJSONString(mapList));
                    JSONObject mapListJSONObject = mapListArray.getJSONObject(0);
                    String particularsOfPayee_xgry_0001 = (String) mapListJSONObject.get("ParticularsOfPayee_xgry_0001");
                    //json.put("particularsOfPayee_xgry_0001",particularsOfPayee_xgry_0001);// 收款人姓名
                    json.put("ParticularsOfPayee",mapList);
                } else { // 插入收款单据对应数据至付款单子表
                    //ParticularsOfPayee_xgry_0001 收款人姓名
                    //ParticularsOfPayee_xgry_0002 付款方式
                    //ParticularsOfPayee_xgry_0003 支付银行
                    //ParticularsOfPayee_xgry_0004 银行卡号
                    //ParticularsOfPayee_xgry_0005 应付金额
                    //ParticularsOfPayee_xgry_0006 实付金额
                    //ParticularsOfPayee_xgry_0007 剩余金额
                    //ParticularsOfPayee_xgry_0008 付款单据类型
                    //ParticularsOfPayee_xgry_0009 付款单据编号
                    StringBuffer stringBuffer = new StringBuffer();
                    stringBuffer.append("INSERT INTO ParticularsOfPayee (PayMentOrder_id,ParticularsOfPayee_xgry_0001,ParticularsOfPayee_xgry_0003,ParticularsOfPayee_xgry_0004,) VALUES (");
//                    String ParticularsOfPayee_xgry_0001 = (String) selectedData.get("TravelExpense_single_person");// 收款人姓名取值差旅报销表的制单人
//                    String ParticularsOfPayee_xgry_0003 = (String) selectedData.get("支付银行");//
//                    String ParticularsOfPayee_xgry_0004 = (String) selectedData.get("银行卡号");//
                    stringBuffer.append(payMentOrder_id + ",'" + ParticularsOfPayee_xgry_0001 + "','" + ParticularsOfPayee_xgry_0003 + "',"+ ParticularsOfPayee_xgry_0004);
                    stringBuffer.append("; SET NOCOUNT ON SELECT SCOPE_IDENTITY() as ParticularsOfPayee_id");
                    //jdbcTemplate.update(stringBuffer.toString());
                    List<Map<String, Object>> mapListSql1 = jdbcTemplate.queryForList(stringBuffer.toString());
                    JSONArray jsonArray6 = JSON.parseArray(JSON.toJSONString(mapListSql1));
                    JSONObject jsonObject2 = jsonArray6.getJSONObject(0);
                    Integer ParticularsOfPayee_id = jsonObject2.getInteger("ParticularsOfPayee_id");
                    String sqlDate1 = "SELECT * FROM particularsOfPayee WHERE particularsOfPayee_id = " + ParticularsOfPayee_id;
                    List<Map<String, Object>> mapList1 = jdbcTemplate.queryForList(sqlDate1);
                    json.put("ParticularsOfPayee",mapList1);
                }
                jsonArray.add(json);
            }
        }
        if ("ExpensesReimbursement".equals(tableName)) {
             // 日常费用报销
                // PayMentOrder_xgry_0001(付款单据类型)
                // PayMentOrder_xgry_0002(付款单据编码)
                // PayMentOrder_xgry_0003(应付总金额)
                // PayMentOrder_xgry_0004(实付总金额)
                // PayMentOrder_xgry_0005(剩余总金额)
                String coding1 = coding.getCoding(); // 编码
                String tableChClassName = (String) jsonObject.get("tableChClassName");// 选中单据的中文表名
                String date = DateTimeUtil.dateToStr(new Date(), "yyyy-MM-dd");
                String PayMentOrder_xgry_0002 = (String) selectedData.get("ExpensesReimbursement_code");//单据编码
                Integer PayMentOrder_xgry_0003 = (Integer) selectedData.get("ExpensesReimbursement_xgry_0004");//应付金额
                String PayMentOrder_xgry_0004 = (String) selectedData.get("");// 实付金额（日常费用报销表没有实付金额字段）
                String ParticularsOfPayee_xgry_0001 = (String) selectedData.get("ExpensesReimbursement_single_person");// 收款人姓名取值日常费用报销表的制单人
//            String ParticularsOfPayee_xgry_0003 = (String) selectedData.get("支付银行");//
//            String ParticularsOfPayee_xgry_0004 = (String) selectedData.get("银行卡号");//

                String ParticularsOfPayee_xgry_0003 = "支付银行";//
                String ParticularsOfPayee_xgry_0004 = "银行卡号";//

                // 根据付款单据类型和付款单据编码查询主表数据
                String selectSql = "SELECT * FROM PayMentOrder WHERE PayMentOrder_xgry_0001 = '" + tableChClassName + "' and PayMentOrder_xgry_0002 = '" + PayMentOrder_xgry_0002 + "'";
                List<Map<String, Object>> mapListSelectSql = jdbcTemplate.queryForList(selectSql);
                if (mapListSelectSql.size() > 0 ) { //有主表数据
                    JSONObject jsonObject1 = new JSONObject();
                    JSONArray mapListArray1 = JSON.parseArray(JSON.toJSONString(mapListSelectSql));
                    JSONObject mapListJSONObject1 = mapListArray1.getJSONObject(0);

                    // 获取主表数据id
                    int payMentOrder_id = (int) mapListJSONObject1.get("PayMentOrder_id");

                    Set<String> strings = mapListJSONObject1.keySet();
                    JSONObject o = new JSONObject();
                    for (String string : strings) {
                        Object o1 = mapListJSONObject1.get(string);
                        o.put(string,o1+"_"+o1);
                    }
                    jsonObject1.put("PayMentOrder",o);
                    jsonObject1.put("cTableName","PayMentOrder");

                    // 新增一条付款单收款人信息
                    StringBuffer stringBuffer = new StringBuffer();
                    stringBuffer.append("INSERT INTO ParticularsOfPayee (PayMentOrder_id,ParticularsOfPayee_xgry_0001,ParticularsOfPayee_xgry_0002,ParticularsOfPayee_xgry_0003,ParticularsOfPayee_xgry_0004," +
                            "ParticularsOfPayee_xgry_0005,ParticularsOfPayee_xgry_0006,ParticularsOfPayee_xgry_0007) VALUES (");
                    stringBuffer.append(payMentOrder_id + ",'" + ParticularsOfPayee_xgry_0001 + "',null,'" + ParticularsOfPayee_xgry_0003 + "','" + ParticularsOfPayee_xgry_0004 + "',null,null,null)");
                    stringBuffer.append("; SET NOCOUNT ON SELECT SCOPE_IDENTITY() as ParticularsOfPayee_id");
                    List<Map<String, Object>> mapListSql1 = jdbcTemplate.queryForList(stringBuffer.toString());
                    JSONArray jsonArray6 = JSON.parseArray(JSON.toJSONString(mapListSql1));
                    JSONObject jsonObject2 = jsonArray6.getJSONObject(0);
                    Integer ParticularsOfPayee_id = jsonObject2.getInteger("ParticularsOfPayee_id");

                    String sqlDate1 = "SELECT * FROM particularsOfPayee WHERE particularsOfPayee_id = " + ParticularsOfPayee_id;
                    List<Map<String, Object>> mapList1 = jdbcTemplate.queryForList(sqlDate1);

                    jsonObject1.put("ParticularsOfPayee",mapList1);

                    jsonArray.add(jsonObject1);

                } else {
                    String sql = "INSERT INTO PayMentOrder (PayMentOrder_code,PayMentOrder_billDate,PayMentOrder_xgry_0001,PayMentOrder_xgry_0002,PayMentOrder_xgry_0003,PayMentOrder_xgry_0004) values ('"
                            + coding1 + "','" + date + "','" + tableChClassName + "','" + PayMentOrder_xgry_0002 + "'," + PayMentOrder_xgry_0003 + "," + PayMentOrder_xgry_0004 + "); SET NOCOUNT ON SELECT SCOPE_IDENTITY() as PayMentOrder_id";

                    List<Map<String, Object>> mapListSql = jdbcTemplate.queryForList(sql);
                    JSONArray jsonArray5 = JSON.parseArray(JSON.toJSONString(mapListSql));
                    JSONObject jsonObject1 = jsonArray5.getJSONObject(0);
                    Integer payMentOrder_id = jsonObject1.getInteger("PayMentOrder_id");

                    String sqlDate = "SELECT * FROM PayMentOrder WHERE PayMentOrder_id = " + payMentOrder_id;
                    List<Map<String, Object>> mapListDate = jdbcTemplate.queryForList(sqlDate);
                    JSONArray mapListArray1 = JSON.parseArray(JSON.toJSONString(mapListDate));
                    JSONObject mapListJSONObject1 = mapListArray1.getJSONObject(0);
                    JSONObject jsonObject11 = new JSONObject();
                    Set<String> strings = mapListJSONObject1.keySet();
                    JSONObject o = new JSONObject();
                    for (String string : strings) {
                        Object o1 = mapListJSONObject1.get(string);
                        o.put(string,o1+"_"+o1);
                    }

                    JSONObject json = new JSONObject();
                    json.put("PayMentOrder",o);

                    json.put("cTableName","PayMentOrder");
                    // 通过付款单据类型、付款单据编码查询收款人详情
                    String sql2 = "SELECT * FROM ParticularsOfPayee WHERE ParticularsOfPayee_xgry_0008 = '" + tableChClassName + "' and ParticularsOfPayee_xgry_0009 = '" + selectedData.getString("ApplicationFund_code") + "'";
                    List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql2);
                    if (mapList.size() > 0) {
                        JSONArray mapListArray = JSON.parseArray(JSON.toJSONString(mapList));
                        JSONObject mapListJSONObject = mapListArray.getJSONObject(0);
                        String particularsOfPayee_xgry_0001 = (String) mapListJSONObject.get("ParticularsOfPayee_xgry_0001");
                        //json.put("particularsOfPayee_xgry_0001",particularsOfPayee_xgry_0001);// 收款人姓名
                        json.put("ParticularsOfPayee",mapList);
                    } else { // 插入收款单据对应数据至付款单子表
                        //ParticularsOfPayee_xgry_0001 收款人姓名
                        //ParticularsOfPayee_xgry_0002 付款方式
                        //ParticularsOfPayee_xgry_0003 支付银行
                        //ParticularsOfPayee_xgry_0004 银行卡号
                        //ParticularsOfPayee_xgry_0005 应付金额
                        //ParticularsOfPayee_xgry_0006 实付金额
                        //ParticularsOfPayee_xgry_0007 剩余金额
                        //ParticularsOfPayee_xgry_0008 付款单据类型
                        //ParticularsOfPayee_xgry_0009 付款单据编号
                        StringBuffer stringBuffer = new StringBuffer();
                        stringBuffer.append("INSERT INTO ParticularsOfPayee (PayMentOrder_id,ParticularsOfPayee_xgry_0001,ParticularsOfPayee_xgry_0003,ParticularsOfPayee_xgry_0004,) VALUES (");
//                    String ParticularsOfPayee_xgry_0001 = (String) selectedData.get("TravelExpense_single_person");// 收款人姓名取值差旅报销表的制单人
//                    String ParticularsOfPayee_xgry_0003 = (String) selectedData.get("支付银行");//
//                    String ParticularsOfPayee_xgry_0004 = (String) selectedData.get("银行卡号");//
                        stringBuffer.append(payMentOrder_id + ",'" + ParticularsOfPayee_xgry_0001 + "','" + ParticularsOfPayee_xgry_0003 + "',"+ ParticularsOfPayee_xgry_0004);
                        stringBuffer.append("; SET NOCOUNT ON SELECT SCOPE_IDENTITY() as ParticularsOfPayee_id");
                        //jdbcTemplate.update(stringBuffer.toString());
                        List<Map<String, Object>> mapListSql1 = jdbcTemplate.queryForList(stringBuffer.toString());
                        JSONArray jsonArray6 = JSON.parseArray(JSON.toJSONString(mapListSql1));
                        JSONObject jsonObject2 = jsonArray6.getJSONObject(0);
                        Integer ParticularsOfPayee_id = jsonObject2.getInteger("ParticularsOfPayee_id");
                        String sqlDate1 = "SELECT * FROM particularsOfPayee WHERE particularsOfPayee_id = " + ParticularsOfPayee_id;
                        List<Map<String, Object>> mapList1 = jdbcTemplate.queryForList(sqlDate1);
                        json.put("ParticularsOfPayee",mapList1);
                    }
                    jsonArray.add(json);
                }
        }
        if ("".equals(tableName)) {
            // PayMentOrder_xgry_0001(付款单据类型)
            // PayMentOrder_xgry_0002(付款单据编码)
            // PayMentOrder_xgry_0003(应付总金额)
            // PayMentOrder_xgry_0004(实付总金额)
            // PayMentOrder_xgry_0005(剩余总金额)
        }
        if ("".equals(tableName)) {
            // PayMentOrder_xgry_0001(付款单据类型)
            // PayMentOrder_xgry_0002(付款单据编码)
            // PayMentOrder_xgry_0003(应付总金额)
            // PayMentOrder_xgry_0004(实付总金额)
            // PayMentOrder_xgry_0005(剩余总金额)
        }
        return jsonArray;
    }

//    @Override
//    public String findSum(String str) throws Exception {
//        String s = sqlData.findSum(str);
//        return s;
//    }
//
//    @Override
//    public String findFormState(String str) throws Exception {
//        String s = sqlData.findFormState(str);
//        return s;
//    }

}
